package git.determinal.template.util;




import cn.hutool.core.date.DateTime;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.Resource;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Description HSSF导入Excel97-2003版本，扩展名为.xls
 * @Author 李家磊
 * @Date 2024/3/7 9:42
 */
public class ExcelUtils {
    //    public static String PATH = "/Users/hello/Desktop/";
//    public static void main(String[] args) throws Exception {
//        //获取文件流
//        FileInputStream inputStream = new FileInputStream(PATH + "用户信息表BigData.xls");
//        //1.创建工作簿,使用excel能操作的这边都看看操作
//        Workbook workbook = new HSSFWorkbook(inputStream);
//        //2.得到表
//        Sheet sheet = workbook.getSheetAt(0);
//        //3.得到行
//        Row row = sheet.getRow(0);
//        //4.得到列
//        Cell cell = row.getCell(0);
//        getValue(cell);
//        inputStream.close();
//    }
    public static String getValue(Cell cell) throws Exception {
        //匹配类型数据
        if (cell != null) {
            CellType cellType = cell.getCellType();
            String cellValue = "";
            switch (cellType) {
                case STRING: //字符串
//                    System.out.print("[String类型]");
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN: //布尔类型
//                    System.out.print("[boolean类型]");
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case BLANK: //空
//                    System.out.print("[BLANK类型]");
                    break;
                case NUMERIC: //数字（日期、普通数字）
//                    System.out.print("[NUMERIC类型]");
                    if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
//                        System.out.print("[日期]");
                        Date date = cell.getDateCellValue();
                        cellValue = new DateTime(date).toString("yyyy-MM-dd");
//                        cellValue = date;
                    } else {
                        //不是日期格式，防止数字过长
//                        System.out.print("[转换为字符串输出]");
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.toString();
                    }
                    break;
                case ERROR:
//                    System.out.print("[数据类型错误]");
                    throw new Exception("数据类型错误");
            }
//            System.out.println(cellValue);
            return cellValue;
        }
        return null;
    }

    /**
     * @Description MultipartFile转Workbook
     * @Param file excel文件
     * @return
     **/
    public static Workbook file2workbook (MultipartFile file) throws IOException {
        InputStream inputStream = null;
        try {
            String originalFilename = file.getOriginalFilename();
            //获取文件流
            inputStream = file.getInputStream();
            Workbook workbook = null;
            if (originalFilename.endsWith("xls")) {
                //1.创建工作簿,使用excel能操作的这边都看看操作
                workbook = new HSSFWorkbook(inputStream);
            }
            if (originalFilename.endsWith("xlsx")) {
                //1.创建工作簿,使用excel能操作的这边都看看操作
                workbook = new XSSFWorkbook(inputStream);
            }
            return workbook;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (inputStream == null) {
                inputStream.close();
            }
        }
        return null;
    }

    /**
     * @Description 导入excel读取数据
     * @Param file excel表文件
     * @Param length 有效行长度
     * @return 有效数据list集合
     **/
    public static List<List<Object>> readExcel(Workbook workbook, Integer length) throws Exception {
        ExcelUtils excelUtils = new ExcelUtils();
        List<List<Object>> rowList = new ArrayList<>();
        //2.得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 获取录入时间
//            java.sql.Date joinTime;
//            Cell timeCell = sheet.getRow(1).getCell(0);
//            if (HSSFDateUtil.isCellDateFormatted(timeCell)) {
//                Date date = timeCell.getDateCellValue();
//                joinTime = new java.sql.Date(date.getTime());
//            } else {
//                throw new Exception("时间格式错误");
//            }
        // 获取表内容，记录进rowList中
//            double numericCellValue = sheet.getRow(3).getCell(9).getNumericCellValue();
//            int rows = sheet.getPhysicalNumberOfRows();
//            for (int i = 3; i < rows; i++) {
//
//            }
        for (int rowIndex = 2; rowIndex < sheet.getPhysicalNumberOfRows() /*StringUtils.isNotBlank(Double.toString(sheet.getRow(rowIndex).getCell(0).getNumericCellValue()))*/; rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            List<Object> cellList = new ArrayList<>();
            for (int cellIndex = 0 ; cellIndex < length ; cellIndex++) {
                Cell cell = row.getCell(cellIndex);
                cellList.add(ExcelUtils.getValue(cell));
            }
//                cellList.add(joinTime);
            rowList.add(cellList);
        }
        return rowList;
    }

    // 将单元格列表写到Servlet
    public static void writeExcel(HttpServletResponse response, Resource resource, List<List<String>> rows, String filename) throws Exception {
        // inputStream和outputStream
        FileInputStream fileInputStream = null;
        ServletOutputStream servletOutputStream = null;
        // 获取source中的文件
        File file = resource.getFile();
        // 将resource写入inputStream
        fileInputStream = new FileInputStream(file);
        // 创建MultipartFile
        MultipartFile multipartFile = FileUtils.file2MultipartFile(file);
        // resource转为文件转为workbook
        Workbook workbook = ExcelUtils.file2workbook(multipartFile);
        // 对workbook进行修改
        Sheet sheet = workbook.getSheetAt(0);
        for ( int i = 0 ; i < rows.size() ; i++ ) {
            for ( int j = 0 ; j < rows.get(0).size() ; j++ ) {
                String value = rows.get(i).get(j);
                Row row = sheet.getRow(i + 2);
                Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                }
                cell.setCellValue(value);
            }
        }
        // response为HttpServletResponse对象
        response.setCharacterEncoding("UTF-8");
        /*response.setContentType("octets/stream");*/
        // 针对不同的浏览器需要有不同的编码，否则文件名会出现乱码
        filename = new String(filename.getBytes("UTF-8"), "ISO8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        // servletOutputStream为OutputStream，需要写出到的目标流
        servletOutputStream = response.getOutputStream();
        // 将工作簿写入响应
        workbook.write(servletOutputStream);
        // 关闭输出流
        servletOutputStream.close();
    }
}


